
using System;
using System.Collections.Generic;
using System.Data;
using System.ComponentModel;
using System.IO;

using Mono.Data.Sqlite;

using stefc.geo.platform;
using stefc.geo.data.model;

namespace stefc.geo.data
{
	/// <summary>
	/// Local Database for GTK-Fraktal based on SQLite
	/// </summary>
	public class DatabaseManager
	{
		
		private static volatile DatabaseManager instance; 

		private SqliteConnection connection;
		
		private DatabaseManager()
		{
			string documents = PlatformManager.Instance.GetHomeFolder();
			
			string db = Path.Combine( documents, "stefc.geo.lib.db3");
			
			if(!File.Exists(db))
				SqliteConnection.CreateFile(db);
				
			this.connection=new SqliteConnection("Data Source=" + db);
			
			// First of all upgrade the schema if neccessary 
			using(DbHelper helper = new DbHelper(this.connection))
			{
				SchemaUpgrade.Upgrade(helper);
			}
		}
		
		public static DatabaseManager Instance
        {
            get
            {
				if (instance==null)
                    instance=new DatabaseManager();
                return instance;
            }
        }
        
        public IDbConnection Connection 
        {
        	get
        	{
        		return this.connection; 
        	}
        }
		
		#region Access the settings table
		public T GetValue<T>(string key, T defaultValue)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				string result=Convert.ToString(helper.ExecuteScalar(String.Format(
					"SELECT value FROM settings WHERE key = '{0}'",key)));
				if(String.IsNullOrEmpty(result))
					return defaultValue;
				
				TypeConverter converter=TypeDescriptor.GetConverter(typeof(T));
				return (T)converter.ConvertFromString(result);
			}
		}
		
		public void SetValue<T>(string key, T value)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				string dbValue = StrLiteral(Convert.ToString(value));
				if(!helper.ExecuteNonQuery(String.Format(
					"UPDATE settings SET value = '{0}' WHERE key = '{1}'", 
					dbValue, key)))
				{
					helper.ExecuteNonQuery(String.Format(
						"INSERT INTO settings (value,key) VALUES ('{0}','{1}')",
						dbValue, key));
				}
			}
		}
		#endregion
		
		#region Point of Interest
		public void UpdatePOI(PointOfInterest poi)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				if(poi.Id==0)
				{
					if(helper.ExecuteNonQuery(String.Format(
						"INSERT INTO pois (Name,Location,Color) VALUES ('{0}','{1}',{2})",
						StrLiteral(poi.Name), StrLiteral(poi.Location.ToString()), poi.Color)))
					{
						poi.Id = Convert.ToInt32(
							helper.ExecuteScalar("SELECT MAX(Id) FROM pois"));
					}
				}
				else
				{
					helper.ExecuteNonQuery(String.Format(
						"UPDATE pois SET Name='{0}',Location='{1}',Color={2} WHERE Id={3}",
						StrLiteral(poi.Name), StrLiteral(poi.Location.ToString()), poi.Color,
						poi.Id));
				}
			}
		}
		
		public List<PointOfInterest> ReadPOIs()
		{
			List<PointOfInterest> result=new List<PointOfInterest>();
			
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				IDataReader reader = helper.ExecuteReader("SELECT * FROM pois ORDER BY Name");
				while(reader.Read())
					result.Add(new PointOfInterest(reader));
			}
			return result;
		}
		
		public PointOfInterest ReadPOI(int id)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				IDataReader reader = helper.ExecuteReader(
					String.Format("SELECT * FROM pois WHERE Id = {0}", id));
				if(reader.Read())
					return new PointOfInterest(reader);
			}
			return null; 
		}
		
		public bool DeletePOI(PointOfInterest poi)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				return helper.ExecuteNonQuery(
					String.Format("DELETE FROM pois WHERE Id = {0}", poi.Id));
			}
		}
		#endregion
		
		#region Contacts CRUD 
		public void UpdateContact(Contact contact)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				if(contact.Id==0)
				{
					if(helper.ExecuteNonQuery(String.Format(
						"INSERT INTO contacts (Name,JId,Level) VALUES ('{0}','{1}',{2})",
						StrLiteral(contact.Name), contact.JId, contact.Level)))
					{
						contact.Id = Convert.ToInt32(
							helper.ExecuteScalar("SELECT MAX(Id) FROM contacts"));
					}
				}
				else
				{
					helper.ExecuteNonQuery(String.Format(
						"UPDATE contacts SET Name='{0}',JId='{1}',Level={2} WHERE Id={3}",
						StrLiteral(contact.Name), contact.JId, contact.Level,
						contact.Id));
				}
			}
		}
		
		public List<Contact> ReadContacts()
		{
			List<Contact> result=new List<Contact>();
			
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				IDataReader reader = helper.ExecuteReader("SELECT * FROM contacts ORDER BY Name");
				while(reader.Read())
					result.Add(new Contact(reader));
			}
			return result;
		}
		
		public Contact ReadContact(int id)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				IDataReader reader = helper.ExecuteReader(
					String.Format("SELECT * FROM contacts WHERE Id = {0}", id));
				if(reader.Read())
					return new Contact(reader);
			}
			return null; 
		}
		
		public bool DeleteContact(Contact contact)
		{
			using(DbHelper helper = new DbHelper(this.Connection))
			{
				return helper.ExecuteNonQuery(
					String.Format("DELETE FROM contacts WHERE Id = {0}", contact.Id));
			}
		}
		#endregion
		
		private string StrLiteral(string s)
		{
			return s.Replace("'", "''");
		}
	}
}
